package com.mmy.dao.hotel;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.hotel.THotelOrder;

public interface THotelOrderRepository extends JpaRepository<THotelOrder, Integer> {
	/**
	 * 根据订单id查找
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tHotelOrder where iID = :iID", nativeQuery = true)
	THotelOrder findByID(@Param("iID") int iID);

	/**
	 * 获取最大流水号
	 * 
	 * @return 返回值说明 流水号
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "SELECT MAX(iID)  FROM tHotelOrder", nativeQuery = true)
	Object getMaxID();

	/**
	 * 获得订单最大编码
	 *
	 * @author 创建人dingxd
	 * @date 修改日期 2020.11.17
	 */
	@Query(value = "SELECT MAX(cOrderID) FROM tHotelOrder WHERE LEFT(cOrderID,16) = CONCAT(:iPlanID,REPLACE(DATE(NOW()),'-',''))", nativeQuery = true)
	Object getMaxCode(@Param("iPlanID") String strPlanID);

	/*
	 * 新增id
	 */

	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tHotelOrder(iID, cOrderID, iReserveUserID, dReserveDate, cReserveName, cReserveTel, iOrderUserID, dOrderDate, cOrderName, cOrderTel, iOrderState, fOrderAmount, iOrderNumber, dOrderInDate, dOrderOutDate, fReceivable, fReceipt, fFloor, fPeer, fPrice, iPayType, cVoucher, iCashierState, dCashierDate, iCashierID, iAccountState, dAccountDate, iAccountID, iInvoiceState, fRefund, iRefundState, iRefundProcess, bRemittanceState, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (:id, NULL, 0, NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, 0, 0, 0, NULL, 0, NULL, 0, 0, NULL, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(@Param("id") int id);

	/*
	 * 根据RoomID查找
	 */
	@Query(value = "select *  from tHotelOrder where left (cOrderID,9) = :iRoomID", nativeQuery = true)
	List<THotelOrder> findByRoomID(@Param("iRoomID") int iRoomID);

	/*
	 * 根据id删除
	 */
	@Transactional
	@Modifying
	@Query(value = "delete from tHotelOrder where tHotelOrder.iID in (:lstID)", nativeQuery = true)
	void delHotelOrder(@Param("lstID") List<Integer> lstID);

	/*
	 * 根据出纳审核状态查找
	 */
	@Query(value = "select *  from tHotelOrder where iCashierState = :strCashierState and left (cOrderID,9) = :iRoomID", nativeQuery = true)
	List<THotelOrder> findByCashierAndRoomID(@Param("strCashierState") String strCashierState,
			@Param("iRoomID") int iRoomID);

	/*
	 * 根据会计审核状态查找
	 */
	@Query(value = "select *  from tHotelOrder where iAccountState = :strAccountState and left (cOrderID,9) = :iRoomID", nativeQuery = true)
	List<THotelOrder> findByAccountAndRoomID(@Param("strAccountState") String strAccountState,
			@Param("iRoomID") int iRoomID);

	/**
	 * 个人中心通过登录人获取酒店预留
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tHotelOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc limit ?2,?3", nativeQuery = true)
	List<THotelOrder> findByUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取酒店订单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tHotelOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<THotelOrder> findByOrderUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取酒店退单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tHotelOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<THotelOrder> findByChargeBackUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取酒店预留 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tHotelOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc", nativeQuery = true)
	int countByUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取酒店退单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tHotelOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc", nativeQuery = true)
	int countByRefundUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取酒店订单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tHotelOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc", nativeQuery = true)
	int countByOrderUserID(int iUserID);

}
